﻿/* Copyright (c) 2010 Lexalytics Inc.

 Permission is hereby granted, free of charge, to any person
 obtaining a copy of this software and associated documentation
 files (the "Software"), to deal in the Software without
 restriction, including without limitation the rights to use,
 copy, modify, merge, publish, distribute, sublicense, and/or sell
 copies of the Software, and to permit persons to whom the
 Software is furnished to do so, subject to the following
 conditions:

 The above copyright notice and this permission notice shall be
 included in all copies or substantial portions of the Software.

 THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
 EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
 OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
 NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
 HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
 WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
 FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
 OTHER DEALINGS IN THE SOFTWARE.
*/

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Data.SqlTypes;
using System.IO;
using System.Text;

namespace Lexalytics4Excel.Database
{
    /// <summary>
    /// Helper class to perform interaction with local SQLite database
    /// </summary>
    public class SqlHelper
    {
        private SQLiteConnection db;
        private SQLiteCommand command;
        private SQLiteDataReader rows;
        private static String[] newDBquery;

        /// <summary>File location of SQLite database</summary>
        public String file {
            get { return location; }   
        }

        private String location;
        private static SqlHelper _dbInUse;

        /// <summary>Flag to indicate if changes have been made to database data</summary>
        public bool changed = false;

        /// <summary>Flag to indicate if a save of the database is needed</summary>
        public bool needsSave
        {
            get { return changed; }
        }

        /// <summary>
        /// the currently active database, used as a singleton when there's only 1 db active. If for some reason multiple
        /// taxonomies need to be used, this object can be set as needed to control which db functions call. For example,
        /// I was considering adding tabs to allow multiple taxonomies to be edited. In that case changing tabs would
        /// change this db reference.
        /// </summary>
        public static SqlHelper dbInUse
        {
            get { return _dbInUse; }
        }

        /// <summary>
        /// Delegate to handle cases in which database needs to be updated to new schema
        /// </summary>
        public delegate void UpdateDBEvent();
        /// <summary>
        /// Event for clients to subscribe to
        /// </summary>
        public UpdateDBEvent updateDb;

        /// <summary>
        /// Default constructor
        /// </summary>
        /// <param name="database">The database</param>
        /// <param name="newDBCommands">Commands to create the database</param>
        /// <param name="updateHandler">Delegate to update old database to new schema</param>
        public SqlHelper(String database, String[] newDBCommands, UpdateDBEvent updateHandler) : this(database, newDBCommands, updateHandler, false)
        {        }

        /// <summary>
        /// Overloaded constructor
        /// </summary>
        /// <param name="database">The database</param>
        /// <param name="newDBCommands">Commands to create the database</param>
        /// <param name="updateHandler">Delegate to update old database to new schema</param>
        /// <param name="newDB">Flag indicating if new database should be created</param>
        public SqlHelper(String database, String[] newDBCommands, UpdateDBEvent updateHandler, bool newDB)
        {
            this.updateDb = updateHandler;
            newDBquery = newDBCommands;
            location = database;
            FileInfo info = new FileInfo(database);
            if (newDB)
            {
                if (info.Exists)
                {
                    try
                    {
                        info.Delete();
                    }
                    catch (Exception err)
                    {
                        System.Diagnostics.Trace.WriteLine(err.Message);
                    }
                }
            }

            String[] nameParts = database.Split(new char[] { '\\', '/', '.' });
            String name = nameParts[nameParts.Length - 2];
            String firstChar = name.Substring(0, 1);
            try
            {
                db = new SQLiteConnection("data source=" + database + ";");
                db.Open();
            } 
            catch (Exception err)
            {
                if (newDB) throw new IOException("Error occured while trying to create file: " + database + ". Make sure your disk is not full and the target folder is valid and write enabled.");
                else throw new IOException("Could not load the file: " + database + ". Is this a valid database? Is it read enabled?"); 
            }
            _dbInUse = this;
            if (newDB)
                CreateDB();
            else if(updateDb != null)
                updateDb.Invoke();
        }

        /// <summary>
        /// Closes the dbInUse and deletes the backup db.
        /// </summary>
        public void close()
        {
            if(command!=null) command.Dispose();
            try
            {
                if (_dbInUse != null)
                {
                    _dbInUse.db.Close();
                    _dbInUse = null;
                }
            }
            catch (Exception e)
            {
                //A DLL didn't release the db properly so now we can't delete it. Not great,
                //but keeping the backup around isn't the end of the world, and we'll delete it
                //next time.
            }
        }

        /// <summary>
        /// Called to save changes to the database. 
        /// </summary>
        public void save()
        {
            if (changed)
            {
                FileInfo info = new FileInfo(location);
                changed = false;
            }
        }

        /// <summary>
        /// Saves the current changes to the database to a new file, and reverts changes in this version of the database.
        /// </summary>
        /// <param name="newName">The file location to save the db to</param>
        public void saveAs(String newName)
        {
            FileInfo info = new FileInfo(location);
            info.CopyTo(newName, true);
            revert();
        }

        /// <summary>
        /// Removes recent changes made to the database.
        /// </summary>
        public void revert()
        {
            if(db.State == ConnectionState.Open)
                db.Close();
            changed = false;
            _dbInUse = new SqlHelper(location, newDBquery, updateDb);

        }

        private bool retried = false;
        /// <summary>
        /// Executes a query on the database. If any parts of the database are coming from the user, you should call
        /// makeSafe on the inputted string first to escape any ' characters.
        /// </summary>
        /// <param name="query">the SQL code to be run (; at end is optional)</param>
        public void execute(String query)
        {

            //nonselects change db, and we record this so we know whether to worry about the backup.
            try
            {
                if (!changed && !query.StartsWith("select", true, null) && !query.Contains("TEMP"))
                    changed = true;
                command = db.CreateCommand();
                command.CommandText = query;
                if (rows != null) rows.Dispose();
                rows = command.ExecuteReader();
                command.Dispose();
                hashedColNames = null;
                retried = false;
            }
            catch (Exception e)
            {
                if (retried)
                {
                    throw e;
                }
                retried = true;
                execute(query);
            }

        }

        /// <summary>
        /// Escapes any ' characters in the input, making it safe to use in execute (no injection attacks or crashing from ' chars).
        /// </summary>
        /// <param name="input">The potentially unsafe input</param>
        /// <returns>Safe version of the string.</returns>
        public String makeSafe(String input)
        {
            if (input == null || input == "") return "";
            /* '' isn't escaped into '''' (so it shows up in the system as ''), which means that if you enter ''George'' you get 'George'
             * double quotes should be inputed as one char ". This is so that if we makeSafe the same string twice we don't get too many 's.
             * If you want to change this, test that nothing is being read from the taxonomy and then makeSafe'd before being inserted back in.
             */
            String intermediate = input.Replace("''", "'");
            return intermediate.Replace("'", "''");
        }

        private Dictionary<string, int> hashedColNames;
        
        private void prepareDataReader()
        {
            hashedColNames = new Dictionary<string, int>();
            System.Data.DataTable table = rows.GetSchemaTable();
            for (int i = 0; i < table.Rows.Count; i++)
            {
                hashedColNames.Add(table.Rows[i].ItemArray[0].ToString(), i);
            }
        }

        /// <summary>
        /// Retrieves list of table names for current database
        /// </summary>
        /// <returns>String array of table names</returns>
        public String[] getTables()
        {
            execute("SELECT name FROM sqlite_master WHERE type='table'");
            return getColumnAsStrings(0);
        }

        /// <summary>
        /// Retrieves list of column names for specified table in current database
        /// </summary>
        /// <param name="table">Table for column list</param>
        /// <returns>String array of column names</returns>
        public String[] getColumnNames(String table)
        {
            //gets the CREATE command that created the table.
            execute("SELECT sql FROM sqlite_master WHERE type='table' AND name='"+table+"'");
            if(nextRow())
            {
                String line = getString(0);
                //We only care about the stuff within () and each column is separated by a ,
                String[] cols = line.Substring(line.IndexOf('(')).Split(',');
                String[] toReturn = new String[cols.Length];
                for (int i = 0; i < cols.Length; i++)
                {
                    String target = cols[i].TrimStart(new char[2] { ' ', '(' });
                    //UNIQUE(column,...) appears like a column, but is actually an instruction. We need to skip it.
                    if (target.StartsWith("UNIQUE"))
                    {
                        String[] copiedArray = new String[i];
                        for (int j = 0; j < i; j++)
                        {
                            copiedArray[j] = toReturn[j];
                        }
                        //UNIQUE has to be the last column according to sqlite syntax, so no need to continue reading.
                        return copiedArray;
                    }
                    //the name comes first, so we take everything from start to the first space.
                    toReturn[i] = target.Substring(0,target.IndexOf(' '));
                }
                return toReturn;
            }
            return null;
        }

        /// <summary>
        /// Gets the next Row from the last execute statement. Throws an exception if there are no more rows.
        /// </summary>
        /// <returns>An array of objects representing the row's data. The objects should be strings and longs</returns>
        public object[] getRow()
        {
            object[] data = new object[rows.FieldCount];
            for (int i = 0; i < data.Length; i++)
            {
                data[i] = rows.GetValue(i);
            }
            return data;
        }

        private int getIndex(String field)
        {
            if (hashedColNames == null) prepareDataReader();
            int index;
            if (!hashedColNames.TryGetValue(field, out index))
            {
                throw new Exception("In the taxonomy database we could not find the field:" + field);
            }
            return index;
        }

        /// <summary>
        /// Returns the integer in the named column from the last nextRow() statement.
        /// </summary>
        /// <param name="field">The name of the column to read</param>
        /// <returns>An integer representing the stored data.</returns>
        public int getInt(String field)
        {
            return rows.GetInt32(getIndex(field));
        }

        /// <summary>
        /// Returns the integer in the specified column from the last nextRow() statement. The column order is not
        /// the schema order, but the order specified in the select statement that created this row.
        /// </summary>
        /// <param name="column">The index of the column to read, in terms of the select statement</param>
        /// <returns>An integer representing the stored data</returns>
        public int getInt(int column)
        {
            try
            {
                return rows.GetInt32(column);
            }
            catch (InvalidCastException)
            {
                return -1;
            }
        }

        /// <summary>
        /// Returns the String in the named column from the last nextRow() statement.
        /// </summary>
        /// <param name="field">The name of the column to read</param>
        /// <returns>A String representing the stored data.</returns>
        public String getString(String field)
        {
            return rows.GetString(getIndex(field));
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="index"></param>
        /// <returns></returns>
        public String[] getColumnAsStrings(int index)
        {
            List<String> strings = new List<string>();
            while (nextRow())
            {
                strings.Add(rows.GetString(index));
            }
            return strings.ToArray();

        }

        /// <summary>
        /// Returns the String in the specified column from the last nextRow() statement. The column order is not
        /// the schema order, but the order specified in the select statement that created this row.
        /// </summary>
        /// <param name="column">The index of the column to read, in terms of the select statement</param>
        /// <returns>A String representing the stored data</returns>
        public String getString(int column)
        {
            try
            {
                return rows.GetString(column);
            }
            catch (InvalidCastException)
            {
                return "Null Entry in DB";
            }
        }

        /// <summary>
        /// Returns the float in the specified column from the last nextRow() statement. The column order is not
        /// the schema order, but the order specified in the select statement that created this row.
        /// </summary>
        /// <param name="field">The index of the column to read, in terms of the select statement</param>
        /// <returns>A float representing the stored data</returns>
        public float getFloat(String field)
        {
            return rows.GetFloat(getIndex(field));
        }

        /// <summary>
        /// Returns the float in the specified column from the last nextRow() statement. The column order is not
        /// the schema order, but the order specified in the select statement that created this row.
        /// </summary>
        /// <param name="column">The index of the column to read, in terms of the select statement</param>
        /// <returns>A float representing the stored data</returns>
        public float getFloat(int column)
        {
            try 
            {
                return rows.GetFloat(column);
            }
            catch (InvalidCastException)
            {
                return -1;
            }
        }

        /// <summary>
        /// Reads the next row from the last executed statement, and returns true or false based on if there was another row to read.
        /// </summary>
        /// <returns>Is there another row of data?</returns>
        public bool nextRow()
        {
            return rows.Read();
        }

        
        /// <summary>
        /// Creates a new database in the file given in the constructor. Shouldn't be called on existing databases.
        /// </summary>
        public void CreateDB()
        {
            command = db.CreateCommand();
            for(int i = 0; i < newDBquery.Length; i++)
            {
                command.CommandText = newDBquery[i];
                command.ExecuteNonQuery();
           }
        }
    }
}
